package org.shams.phonebook;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @author <a href="mailto:m.h.shams@gmail.com">M. H. Shamsi</a>
 * @version 1.0.0
 *          Date Jan 15, 2008
 */

public class DataBaseUtils {
    private static final String DB_DRIVER = "org.hsqldb.jdbcDriver";
    private static final String DB_URL = "jdbc:hsqldb:file:/home/shams/Project/t5m/phb/phbdb";
    private static final String DB_USERNAME = "sa";
    private static final String DB_PASSWORD = "";

    private static Connection connection;

    public static void main(String[] args) throws Exception {
        createDatabase();
    }

    public static void createDatabase() throws Exception {
        init();
        ddlscripts();
        destroy();
    }

    public static void clearDatabase() throws Exception {
        init();
        dmlscripts();
        destroy();
    }

    private static void init() throws Exception {
        Class.forName(DB_DRIVER);
        connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        connection.setAutoCommit(false);
    }

    private static void destroy() throws Exception {
        connection.close();
    }

    private static void dmlscripts() throws Exception {
        Statement statement = connection.createStatement();
        statement.executeUpdate("delete from _item");
        statement.executeUpdate("delete from _user");
        connection.commit();
        statement.execute("SHUTDOWN");
        statement.close();
        connection.close();
    }

    private static void ddlscripts() throws Exception {
        Statement statement = connection.createStatement();
        statement.executeUpdate("drop table _item if exists");
        statement.executeUpdate("create table _item(" +
                "id      int generated by default as identity (start with 1, increment by 1) not null primary key," +
                "name    varchar(40) not null," +
                "family  varchar(40) not null," +
                "phone   varchar(15) not null," +
                "mobile  varchar(15)," +
                "mail  varchar(40)," +
                "address varchar(200)" +
                ")");

        statement.executeUpdate("drop table _user if exists");
        statement.executeUpdate("create cached table _user(" +
                "id        int generated by default as identity (start with 1, increment by 1) not null primary key," +
                "name      varchar(100) not null," +
                "username  varchar(40)  not null," +
                "password  varchar(40)  not null," +
                "constraint uuser unique (username)" +
                ")");

        connection.commit();
        statement.execute("SHUTDOWN");
        statement.close();
        connection.close();
    }
}
